﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DacSanMienTayOBJ
{
    public class ArticleBusiness
    {
        public static bool InsertArticle(Article art, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "insert into article(TypeID,AdminID,Title,Summary,Metakeyword,ImageArticle,ContentArticle,CreateDate,Priority,IsVisible) values(@typeID,@adminID,@title,@summary,@metakey,@imageArticle,@contentArticle,@createDate,@priority,@isVisible)";
                SqlParameter[] pa = new SqlParameter[10];
                pa[0] = new SqlParameter("@typeID", art.TypeID);
                pa[1] = new SqlParameter("@adminID", art.AdminID);
                pa[2] = new SqlParameter("@title", art.Title);
                pa[3] = new SqlParameter("@summary", art.Summary);
                pa[4] = new SqlParameter("@contentArticle", art.ContentArticle);
                pa[5] = new SqlParameter("@imageArticle", art.ImageArticle);
                pa[6] = new SqlParameter("@createDate", art.CreateDate);
                pa[7] = new SqlParameter("@priority", art.Priority);
                pa[8] = new SqlParameter("@isVisible", art.IsVisible);
                pa[9] = new SqlParameter("@metakey", art.Metakeyword);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool UpdateArticle(int articleID,Article art, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "update article set TypeID=@typeID,Title=@title,Summary=@summary,Metakeyword=@metakey,ImageArticle=@imageArticle,ContentArticle=@contentArticle where ArticleID=@articleID";
                SqlParameter[] pa = new SqlParameter[7];
                pa[0] = new SqlParameter("@typeID", art.TypeID);
                pa[1] = new SqlParameter("@articleID", articleID);
                pa[2] = new SqlParameter("@title", art.Title);
                pa[3] = new SqlParameter("@summary", art.Summary);
                pa[4] = new SqlParameter("@contentArticle", art.ContentArticle);
                pa[5] = new SqlParameter("@imageArticle", art.ImageArticle);
                pa[6] = new SqlParameter("@metakey", art.Metakeyword);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool UpdateArticleVisible(int articleID, int isVisible, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "update article set IsVisible=@isVisible where ArticleID=@articleID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@isVisible",isVisible);
                pa[1] = new SqlParameter("@articleID",articleID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool UpdateArticlePriority(int articleID, int priority, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "update article set Priority=@priority where ArticleID=@articleID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@priority", priority);
                pa[1] = new SqlParameter("@articleID", articleID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Article> ListAllArticle(int start,int end,ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Article> list = new List<Article>();
                string sql = "select *from(SELECT ROW_NUMBER() OVER (ORDER BY ArticleID DESC) as Row,art.ArticleID,ta.TypeName,ad.Username,art.TypeID,art.AdminID,art.Title,art.Summary,art.Metakeyword,art.ImageArticle,art.ContentArticle,art.CreateDate,art.Priority,art.IsVisible from  article art,typearticle ta,admin ad where art.TypeID=ta.TypeID and art.AdminID=ad.AdminID) as PaggingArticle where Row>=@startIndex and Row<=@endIndex";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@startIndex",start);
                pa[1] = new SqlParameter("@endIndex",end);
                SqlCommand command = helper.GetCommand(sql,pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Article art = new Article();
                    art.ArticleID = int.Parse(reader["ArticleID"].ToString());
                    art.AdminID = int.Parse(reader["AdminID"].ToString());
                    art.ContentArticle = reader["ContentArticle"].ToString();
                    art.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    art.ImageArticle = reader["ImageArticle"].ToString();
                    art.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    art.Priority = int.Parse(reader["Priority"].ToString());
                    art.Summary = reader["Summary"].ToString();
                    art.Title = reader["Title"].ToString();
                    art.TypeID = int.Parse(reader["TypeID"].ToString());
                    art.TypeName = reader["TypeName"].ToString();
                    art.Username = reader["UserName"].ToString();
                    art.Metakeyword = reader["Metakeyword"].ToString();
                    list.Add(art);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Article> ListAllArticle(int visible,int start,int end,ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Article> list = new List<Article>();
                string sql = "select *from(SELECT ROW_NUMBER() OVER (ORDER BY ArticleID DESC) as Row,art.ArticleID,ta.TypeName,ad.Username,art.TypeID,art.AdminID,art.Title,art.Summary,art.Metakeyword,art.ImageArticle,art.ContentArticle,art.CreateDate,art.Priority,art.IsVisible from  article art,typearticle ta,admin ad where art.TypeID=ta.TypeID and art.AdminID=ad.AdminID and art.IsVisible=@isVisible) as PaggingArticle where Row>=@startIndex and Row<=@endIndex";
                SqlParameter[] pa = new SqlParameter[3];
                pa[0] = new SqlParameter("@startIndex", start);
                pa[1] = new SqlParameter("@endIndex", end);
                pa[2] = new SqlParameter("@isVisible",visible);
                SqlCommand command = helper.GetCommand(sql,pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Article art = new Article();
                    art.ArticleID = int.Parse(reader["ArticleID"].ToString());
                    art.AdminID = int.Parse(reader["AdminID"].ToString());
                    art.ContentArticle = reader["ContentArticle"].ToString();
                    art.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    art.ImageArticle = reader["ImageArticle"].ToString();
                    art.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    art.Priority = int.Parse(reader["Priority"].ToString());
                    art.Summary = reader["Summary"].ToString();
                    art.Title = reader["Title"].ToString();
                    art.TypeID = int.Parse(reader["TypeID"].ToString());
                    art.TypeName = reader["TypeName"].ToString();
                    art.Username = reader["UserName"].ToString();
                    art.Metakeyword = reader["Metakeyword"].ToString();
                    list.Add(art);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Article> ListAllArticleFollowTopic(int typeID,int start,int end,ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Article> list = new List<Article>();
                string sql = "select *from(SELECT ROW_NUMBER() OVER (ORDER BY ArticleID DESC) as Row,art.ArticleID,ta.TypeName,ad.Username,art.TypeID,art.AdminID,art.Title,art.Summary,art.Metakeyword,art.ImageArticle,art.ContentArticle,art.CreateDate,art.Priority,art.IsVisible from  article art,typearticle ta,admin ad where art.TypeID=ta.TypeID and art.AdminID=ad.AdminID and art.TypeID=@typeID) as PaggingArticle where Row>=@startIndex and Row<=@endIndex";
                SqlParameter[] pa = new SqlParameter[3];
                pa[0] = new SqlParameter("@startIndex", start);
                pa[1] = new SqlParameter("@endIndex", end);
                pa[2] = new SqlParameter("@typeID",typeID);
                SqlCommand command = helper.GetCommand(sql,pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Article art = new Article();
                    art.ArticleID = int.Parse(reader["ArticleID"].ToString());
                    art.AdminID = int.Parse(reader["AdminID"].ToString());
                    art.ContentArticle = reader["ContentArticle"].ToString();
                    art.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    art.ImageArticle = reader["ImageArticle"].ToString();
                    art.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    art.Priority = int.Parse(reader["Priority"].ToString());
                    art.Summary = reader["Summary"].ToString();
                    art.Title = reader["Title"].ToString();
                    art.TypeID = int.Parse(reader["TypeID"].ToString());
                    art.TypeName = reader["TypeName"].ToString();
                    art.Username = reader["UserName"].ToString();
                    art.Metakeyword = reader["Metakeyword"].ToString();
                    list.Add(art);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Article> ListAllArticleFollowTopic(int typeID,int visible,int start,int end, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Article> list = new List<Article>();
                string sql = "select *from(SELECT ROW_NUMBER() OVER (ORDER BY ArticleID DESC) as Row,art.ArticleID,ta.TypeName,ad.Username,art.TypeID,art.AdminID,art.Title,art.Summary,art.Metakeyword,art.ImageArticle,art.ContentArticle,art.CreateDate,art.Priority,art.IsVisible from  article art,typearticle ta,admin ad where art.TypeID=ta.TypeID and art.AdminID=ad.AdminID and art.TypeID=@typeID and art.IsVisible=@isVisible) as PaggingArticle where Row>=@startIndex and Row<=@endIndex";
                SqlParameter[] pa = new SqlParameter[4];
                pa[0] = new SqlParameter("@startIndex", start);
                pa[1] = new SqlParameter("@endIndex", end);
                pa[2] = new SqlParameter("@typeID", typeID);
                pa[3] = new SqlParameter("@isVisible",visible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Article art = new Article();
                    art.ArticleID = int.Parse(reader["ArticleID"].ToString());
                    art.AdminID = int.Parse(reader["AdminID"].ToString());
                    art.ContentArticle = reader["ContentArticle"].ToString();
                    art.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    art.ImageArticle = reader["ImageArticle"].ToString();
                    art.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    art.Priority = int.Parse(reader["Priority"].ToString());
                    art.Summary = reader["Summary"].ToString();
                    art.Title = reader["Title"].ToString();
                    art.TypeID = int.Parse(reader["TypeID"].ToString());
                    art.TypeName = reader["TypeName"].ToString();
                    art.Username = reader["UserName"].ToString();
                    art.Metakeyword = reader["Metakeyword"].ToString();
                    list.Add(art);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static int CountAllArticle(ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                int count = 0;
                string sql = "select count(ArticleID) as totalArticle from article";
                SqlCommand command = helper.GetCommandNonParameter(sql, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    count = int.Parse(reader["totalArticle"].ToString());
                }
                return count;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return 0;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static int CountAllArticle(int visible,ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                int count = 0;
                string sql = "select count(ArticleID) as totalArticle from article where IsVisible=@isVisible";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@isVisible",visible);
                SqlCommand command = helper.GetCommand(sql,pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    count = int.Parse(reader["totalArticle"].ToString());
                }
                return count;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return 0;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static int CountAllArticleFollowType(int typeID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                int count = 0;
                string sql = "select count(ArticleID) as totalArticle from article where TypeID=@typeID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@typeID", typeID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    count = int.Parse(reader["totalArticle"].ToString());
                }
                return count;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return 0;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static int CountAllArticle(int typeID,int visible, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                int count = 0;
                string sql = "select count(ArticleID) as totalArticle from article where TypeID=@typeID and IsVisible=@isVisible";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@typeID", typeID);
                pa[1] = new SqlParameter("@isVisible",visible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    count = int.Parse(reader["totalArticle"].ToString());
                }
                return count;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return 0;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static Article GetArticleDetail(int articleID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                Article art = null;
                string sql = "select *from article where ArticleID=@articleID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@articleID",articleID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    art = new Article();
                    art.ArticleID = int.Parse(reader["ArticleID"].ToString());
                    art.AdminID = int.Parse(reader["AdminID"].ToString());
                    art.ContentArticle = reader["ContentArticle"].ToString();
                    art.CreateDate = DateTime.Parse(reader["CreateDate"].ToString());
                    art.ImageArticle = reader["ImageArticle"].ToString();
                    art.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    art.Priority = int.Parse(reader["Priority"].ToString());
                    art.Summary = reader["Summary"].ToString();
                    art.Title = reader["Title"].ToString();
                    art.TypeID = int.Parse(reader["TypeID"].ToString());
                    art.Metakeyword = reader["Metakeyword"].ToString();
                }
                return art;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool InsertArticleProduct(int productID, int articleID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "insert into productarticle(ProductID,ArticleID) values(@productID,@articleID)";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@productID",productID);
                pa[1] = new SqlParameter("@articleID",articleID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool DeleteArticleProductByArticleID(int articleID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "delete productarticle where ArticleID=@articleID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[1] = new SqlParameter("@articleID", articleID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool DeleteArticleProductByProductID(int productID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "delete productarticle where ProductID=@productID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[1] = new SqlParameter("@productID", productID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }
    }
}
